/**
 * 
这段代码定义了两个类：Mysql 和 Db，用于数据库操作。以下是代码的主要作用：

Mysql 类：
该类提供了两个异步方法 query 和 execute，用于执行数据库查询和操作。
query 方法执行 SQL 查询语句，并返回查询结果。
execute 方法执行 SQL 操作语句（如插入、更新、删除），并返回操作结果。
Db 类：

Db 类继承了 Mysql 类，表示针对特定表的数据库操作。
构造函数接受一个表名，并将其保存在 this.table 中。
提供了一系列异步方法，用于对该表进行常见的数据库操作：
create: 插入新数据。
delete: 删除数据。
update: 更新数据。
get: 查询单条数据。
getList: 查询数据列表，支持条件筛选和分页。
where: 自定义条件查询。

封装了常见的数据库操作，使得在其他模块中可以轻松地使用这些功能，而不必直接处理数据库连接和 SQL 语句的细节
 */


// Db 类被用作其他处理特定表的类的基类
const connection = require("../app/database");
const retCode = require("../constants/retcode");
const formatUtcString = require("./date-format");

// Mysql 类用于执行数据库查询和操作
class Mysql {
  // 执行查询操作
  async query(sql, args) {
    try {
      const [result] = await connection.query(sql, args);
      return result;
    } catch (error) {
      console.log(error);
      return retCode.ServerError;
    }
  }

  // 执行数据库操作
  async execute(sql, args) {
    try {
      const [result] = await connection.execute(sql, args);
      return result;
    } catch (error) {
      console.log(error);
      return retCode.ServerError;
    }
  }
}

// Db 类继承自 Mysql 类，用于对特定表进行数据操作
class Db extends Mysql {
  constructor(table) {
    super();
    this.table = table;
  }

  // 创建数据
  async create(obj) {
    const statement = `INSERT INTO \`${this.table}\` SET ?;`;
    const result = await this.query(statement, [obj]);
    let body = {};
    if (result.affectedRows > 0 && !!result.affectedRows) {
      body.code = 1;
      body.message = "创建成功";
      body.data = result;
    } else {
      body.code = 0;
      body.message = "创建失败";
    }
    return body;
  }

  // 删除数据
  async delete(id) {
    const statement = `DELETE FROM \`${this.table}\` WHERE id=?`;
    const result = await this.execute(statement, [id]);
    let body = {};
    if (result.affectedRows > 0) {
      body.code = 200;
      body.message = `删除${id}成功`;
    } else {
      body.code = 0;
      body.message = `删除${id}失败`;
    }
    return body;
  }

  // 修改数据
  async update(userId, data) {
    try {
      const statement = `UPDATE \`${this.table}\` SET ? WHERE id = ?;`;
      const result = await this.query(statement, [data, userId]);
      let body = {};

      if (result.affectedRows > 0) {
        body.code = 200;
        body.message = `更新id=${userId}的数据成功`;
        body.data = result;
      } else {
        body.code = 0;
        body.message = "更新失败";
        body.data = [];
      }

      return body;
    } catch (error) {
      console.error(`Error updating data for id=${userId}:`, error);
      throw error; // 重新抛出错误以便上层处理
    }
  }


  // 查看数据库单条数据
  async get(id) {
    const statement = `SELECT * FROM \`${this.table}\` WHERE id = ?;`;
    const [result] = await this.execute(statement, [id]);
    let body = {};
    console.log(result);
    body.code = 200;
    body.message = "查询成功";
    body.data = result || [];
    return body;
  }


  // 查询数据列表
  async getList(obj) {
    const { offset = 0, size = 100, createAt = [], ...rest } = obj;
    const arr = [];
    for (const key in rest) {
      if (typeof rest[key] === "number" || rest[key]) {
        // arr.push(`${key}="${rest[key]}"`);
        arr.push(`${key} LIKE "%${rest[key]}%"`);
      }
    }
    let newArr = arr.join(" AND ");

    // 时间查询
    if (createAt.length > 0 && newArr.length > 0) {
      let a = formatUtcString(createAt[0]);
      let b = formatUtcString(createAt[1]);
      newArr = `${newArr} AND createAt BETWEEN '${a}' AND '${b}'`;
    }

    if (createAt.length > 0 && newArr.length === 0) {
      let a = formatUtcString(createAt[0]);
      let b = formatUtcString(createAt[1]);
      newArr = `createAt BETWEEN '${a}' AND '${b}'`;
    }

    let body = {};
    if (newArr) {
      const statement = `SELECT * FROM \`${this.table}\` WHERE ${newArr} ORDER BY id DESC LIMIT ?, ?;`;
      const result = await this.query(statement, [offset, size]);
      const statement2 = `SELECT COUNT(*) AS totalCount FROM \`${this.table}\` WHERE ${newArr}`;
      const [result2] = await this.query(statement2);
      const data = {
        list: result,
        totalCount: result2.totalCount,
      };
      body.code = 1;
      body.message = "查询成功";
      body.data = data;
      return body;
    } else {
      const statement = `SELECT * FROM \`${this.table}\` ORDER BY id DESC LIMIT ?, ?;`;
      const result = await this.query(statement, [offset, size]);
      const statement2 = `SELECT COUNT(*) AS totalCount FROM \`${this.table}\``;
      const [result2] = await this.query(statement2);
      const data = {
        list: result,
        totalCount: result2.totalCount,
      };
      body.code = 200;
      body.message = "查询成功";
      body.data = data;
      return body;
    }
  }

  // 自定义条件查询
  async where(sql, arg = []) {
    let body = {};
    const result = await this.query(sql, arg);
    body.code = 200;
    body.message = "查询成功";
    body.data = result;
    return body;
  }
  // 根据传入type和count查找列表
  async search(type) {
    try {
      let body = {};
      const statement = `SELECT * FROM \`${this.table}\` WHERE type=?;`;
      console.log('statement=>', statement)
      const [result] = await connection.execute(statement, [type]);
      const data = {
        list: result,
      };
      body.code = 200;
      body.message = "查询成功";
      body.data = data || [];
      return body;
    } catch (error) {
      console.error('UserService - findAvatarById:', error);
      throw error; // 抛出异常，让调用者处理
    }
  }
  //将图像信息更新到表中
  async updatePhoto(id, imgUrl) {
    try {
      const statement = `UPDATE \`${this.table}\` SET img = ? WHERE id = ?;`;
      const result = await this.query(statement, [imgUrl, id]);
      let body = {};

      if (result.affectedRows > 0) {
        body.code = 200;
        body.message = `更新图片id=${id}的数据成功~`;
        body.data = {
          imgUrl
        }
      } else {
        body.code = 0;
        body.message = "更新失败";
        body.data = [];
      }

      return body;
    } catch (error) {
      console.error(`Error updating photo for id=${id}:`, error);
      throw error; // 重新抛出错误以便上层处理
    }
  }
  // 根据分类ID查找照片信息
  async getPhotoById(id) {
    try {
      const statement = `SELECT img FROM \`${this.table}\` WHERE id=?;`;
      console.log(statement, id)
      const [result] = await this.execute(statement, [id]);

      let body = {};
      body.code = 200;
      body.message = "查询成功";
      body.data = result || [];
      console.log('body.data=>', body)
      return body;

    } catch (error) {
      console.error('UserService - getPhotoById:', error);
      throw error; // 抛出异常，让调用者处理
    }
  }

}

module.exports = Db;
